/**
 * Copyright 2012 Simple-Jdbc-Lightweight

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package org.mlu.jdbc.simple;

import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.mlu.jdbc.simple.mapper.CustomBeanMapper;
import org.mlu.jdbc.simple.mapper.PropertyBeanMapper;
import org.mlu.jdbc.simple.utils.JdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * Exposes jdbc operations thereby delegating opening and closing of database resources.
 * 
 * @author Mlungisi Sincuba
 * @since 1.0
 */
public class SimpleJdbc {

    private DataSource dataSource;
    private static final Logger logger = LoggerFactory.getLogger(SimpleJdbc.class);

    /**
     * Uses db.properties from class path or project path;
     * 
     * Used mainly for testing and web applications with default filename
     */
    public SimpleJdbc() {
        if (dataSource == null) {
            try {
                dataSource = DataSourceDetails.getDataSource();
            } catch (FileNotFoundException ex) {
                if (dataSource == null) {
                    dataSource = DataSourceDetails.getDataSourceFromClassPath();
                }
                logger.debug("File 'db.properties' was not found");
            }
            if (dataSource == null) {
                dataSource = DataSourceDetails.getDataSourceFromClassPath();
            }
        }
        logger.debug("Using datasource: " + dataSource);
    }
    
    /**
     * 
     * Used main on stand-alone applications
     * 
     * @param fileName properties file on project path or class path
     */
    public SimpleJdbc(String fileName) {
        if (fileName == null) {
            fileName = "db.properties";
        }
        if (dataSource == null) {
            try{
                dataSource = DataSourceDetails.getDataSource(fileName);
            } catch(FileNotFoundException ex) {
                if (dataSource == null) {
                    fileName = fileName.substring(0, fileName.indexOf("."));
                    dataSource = DataSourceDetails.getDataSourceFromClassPath(fileName);
                }
                logger.debug("File " + fileName + " was not found");
            }
            
        }
        logger.debug("Using datasource: " + dataSource);
    }
    
    /**
     * 
     * @param input which contains properties file from either class path or WEB-INF path
     * 
     * Usage example:
     * 
     * InputStream input = getServletContext().getResourceAsStream("/WEB-INF/db.properties");<br/>
     * SimpleJdbc x = new SimpleJdbc(input);
     * 
     * 
     */
    public SimpleJdbc(InputStream input) {
        if (dataSource == null) {
            dataSource = DataSourceDetails.getDataSourceForWeb(input);
        }
        logger.debug("Using datasource: " + dataSource);
    }
    
    /**
     * 
     * @param dataSource DataSource for database. It must be on properties file with keys/values like <br/>
     * <ul>
     * <li>driver=org.postgresql.Driver</li>
     * <li>url=jdbc:postgresql://localhost:4444/gain</li>
     * <li>user=gain</li>
     * <li>pwd=gain</li>
     * <li>minPool=1</li>
     * <li>maxPool=15</li>
     * <li>maxSize=30</li>
     * </ul>
     */
    public SimpleJdbc(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     *
     * @param sql query
     * @param type to cast to
     * @return object for a particular type
     *
     * @see org.apache.commons.dbutils.QueryRunner#query(java.lang.String,
     * org.apache.commons.dbutils.ResultSetHandler)
     */
    public <T extends Object> T queryForObject(String sql, Class<T> type) {
        ResultSet rs = getResultSet(sql);
        T t = null;
        try {
            t = getType(rs, t, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        }
        return t;
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to 
     * @param params arguments to sql
     * @return object for a particular type
     */
    public <T> T queryForObject(String sql, Class<T> type, Object... params) {
        ResultSet rs = getResultSet(sql, params);
        T t = null;
        try {
            t = getType(rs, t, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        }
        return t;
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param resultSetColumnBeanProperty
     * @param type to cast to
     * @return object for a particular type
     */
    public <T> T queryForObject(String sql, Map<String, String> resultSetColumnBeanProperty, Class<T> type) {
        return new CustomBeanMapper().mapRow(getDataSource(), sql, type, resultSetColumnBeanProperty);
    }

    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to
     * @param params arguments to sql
     * @return object for a particular type
     */
    public <T> T queryForBean(String sql, Class<T> type, Object... params) {
        return new PropertyBeanMapper().mapRow(getDataSource(), sql, type, params);
    }

    /**
     * 
     * @param <T>
     * @param sql
     * @param type
     * @param params
     * @return 
     */
    public <T> List<T> queryForBeanList(String sql, Class<T> type, Object... params) {
        return new PropertyBeanMapper().mapBeanList(dataSource, sql, type, params);
    }
    
    /**
     * 
     * @param <T>
     * @param sql to query database
     * @param type to cast to
     * @return object for a particular type
     */
    public <T> List<T> queryForBeanList(String sql, Class<T> type) {
        return new PropertyBeanMapper().mapBeanList(dataSource, sql, type, null);
    }

    /**
     * 
     * @param sql ddl query to database
     * @return number of rows affected
     */
    public int update(String sql) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            return run.update(sql);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed with error code: " + ex.getErrorCode() + " and state: " + ex.getSQLState(), ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param con database connection
     * @return number of rows affected
     */
    public int update(String sql, Connection con) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            return run.update(con, sql);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params arguments to sql
     * @return number of rows affected
     */
    public int update(String sql, Object... params) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            int updated = run.update(sql, params);
            return updated;
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param con database connection
     * @param params arguments to sql
     * @return number of rows affected
     */
    public int update(String sql, Connection con, Object... params) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            return run.update(con, sql, params);
        } catch (SQLException ex) {
            logger.error("update with sql: " + sql + " failed.", ex);
        }
        return 0;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params arguments to sql
     * @return number of rows affected per batch
     */
    public int[] batch(String sql, Object[][] params) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            return run.batch(sql, params);
        } catch (SQLException ex) {
            logger.error("batch with sql: " + sql + " failed.", ex);
        }
        return null;
    }

    /**
     * 
     * @param sql ddl query to database
     * @param params
     * @param con database connection
     * @return number of rows affected per batch
     */
    public int[] batch(String sql, Object[][] params, Connection con) {
        QueryRunner run = new QueryRunner(getDataSource());
        try {
            return run.batch(con, sql, params);
        } catch (SQLException ex) {
            logger.error("batch with sql: " + sql + " failed.", ex);
        }
        return null;
    }

    private ResultSet getResultSet(String sql) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection(getDataSource());
            statement = JdbcUtils.getPreparedStatement(connection, sql);
            rs = statement.executeQuery();
        } catch (SQLException ex) {
            logger.error("getResultSet could not be run", ex);
        }
        return rs;
    }
    
    private ResultSet getResultSet(String sql, Object ...params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection(getDataSource());
            statement = JdbcUtils.getPreparedStatement(connection, sql, params);
            rs = statement.executeQuery();
        } catch (SQLException ex) {
            logger.error("getResultSet could not be run", ex);
        }
        return rs;
    }

    private <T extends Object> T getType(ResultSet rs, T t, Class<T> type) throws SQLException {
        if (rs.next()) {
            t = (T) JdbcUtils.getResultSetValue(rs, 1, type);
        }
        return t;
    }

    /**
     * @return the dataSource
     */
    public DataSource getDataSource() {
        return dataSource;
    }

    /**
     * @param dataSource the dataSource to set
     */
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
}
